package com.taylor.core.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;

import com.taylor.core.bean.CustomClubRelationBean;
import com.taylor.core.bean.LessonAbstractSearchConditionBean;
import com.taylor.core.bean.LessonEventBean;
import com.taylor.core.bean.LessonEventSearchConditionBean;
import com.taylor.core.bean.LessonInstPlaceRelationBean;
import com.taylor.core.bean.LessonInstPlaceRelationSearchConditionBean;
import com.taylor.core.bean.LessonInstructorBean;
import com.taylor.core.bean.LessonInstructorSearchConditionBean;
import com.taylor.core.bean.LessonPlaceBean;
import com.taylor.core.bean.LessonPlaceSearchConditionBean;
import com.taylor.core.bean.LessonReservationBean;
import com.taylor.core.bean.LessonReservationSearchConditionBean;
import com.taylor.core.bean.Prefecture;
import com.taylor.core.bean.UserMaster;
import com.taylor.core.bean.Ziptable;
import com.taylor.core.dao.DBUtils;
import com.taylor.core.dao.DaoSqlConfigure;
import com.taylor.core.db.DBException;
import com.taylor.core.db.DBSessionSupport;
import com.taylor.user.business.UF1OperateExcetion;

/**
 * LessonEvent関連テーブルへの操作を行うDAO。 関連クラスは LessonEventBean, LessonInstructorBean,
 * LessonPlaceBean, LessonInstPlaceRelationBean, LessonReservationBean
 * 
 * @author 西田正洋
 * 
 */
public class LessonEventDAO extends DBSessionSupport {
	static Logger log = Logger.getLogger(LessonEventDAO.class.getName());
	Connection conn;

	/*------------------------------------------------------------
	 *  共通Select文 Common select SQL 
	 ------------------------------------------------------------*/

	/**
	 * select文
	 * 
	 * @param LessonAbstractSearchConditionBean
	 * @return List of LessonAbstractSearchConditionBean.getResultBeanClass()
	 * @throws UF1OperateExcetion
	 */
	public List select(LessonAbstractSearchConditionBean laScBean)
			throws UF1OperateExcetion {
		return laScBean.select();
	}

	/*------------------------------------------------------------
	 *  インストラクター Instructor 
	 ------------------------------------------------------------*/

	/**
	 * select文
	 * 
	 * @param liscBean
	 *            検索条件クラス。nullの場合、全検索。
	 * @return List of LessonInstructorBean
	 * @throws UF1OperateExcetion
	 */
	/*
	 * public List selectInstructor(LessonInstructorSearchConditionBean
	 * liscBean) throws UF1OperateExcetion{ //検索条件のSQLを追加 Object[] params =
	 * null; String strSql =
	 * LessonInstructorSearchConditionBean.getStrSqlSelect(); if(liscBean !=
	 * null){ strSql += liscBean.getSearchConditionSQL(); params =
	 * liscBean.getSearchConditionObjectList(); }
	 * 
	 * try { return DBUtils.select(strSql, LessonInstructorBean.class, params,
	 * getConnection()); } catch (SQLException e) { throw new
	 * UF1OperateExcetion(e); } catch (DBException e) { throw new
	 * UF1OperateExcetion(e); } }
	 */
	/**
	 * select文
	 * 
	 * @param li_id
	 *            検索する
	 * @return List of LessonInstructorBean
	 * @throws UF1OperateExcetion
	 */
	public LessonInstructorBean selectInstructor(Integer li_id)
			throws UF1OperateExcetion {
		LessonInstructorSearchConditionBean liscBean = new LessonInstructorSearchConditionBean(
				li_id);
		List lstRet = select(liscBean);
		if (lstRet.size() == 0) {
			return null;
		} else {
			return (LessonInstructorBean) lstRet.get(0);
		}
	}

	/**
	 * select文 全検索を行う
	 * 
	 * @return List of LessonInstructorBean
	 * @throws UF1OperateExcetion
	 */
	public List selectInstructor() throws UF1OperateExcetion {
		LessonInstructorSearchConditionBean liscBean = new LessonInstructorSearchConditionBean();
		return select(liscBean);
	}

	/**
	 * select文 全検索を行う
	 * 
	 * @return List of LessonInstructorBean
	 * @throws UF1OperateExcetion
	 */
	public List selectInstructor_cond() throws UF1OperateExcetion {
		LessonInstructorSearchConditionBean liscBean = new LessonInstructorSearchConditionBean();
		liscBean.setLi_active_flag(new Boolean(true));
		return select(liscBean);
	}

	/**
	 * select文 IDと名前のみ全検索を行う
	 * 
	 * @return List of LessonInstructorBean (only 'li_id' and 'li_name' are
	 *         filled)
	 * @throws UF1OperateExcetion
	 */
	public List selectInstructorIDName() throws UF1OperateExcetion {
		String strSql = "select li_id, (li_family_name || ' ' || li_first_name) as li_name from lesson_instructor";

		try {
			return DBUtils.select(strSql, LessonInstructorBean.class, null,
					getConnection());
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * インサート insert
	 * 
	 * @param liBean
	 * @return Success:true, Failure:false
	 * @throws UF1OperateExcetion
	 */
	public boolean insertInstructor(LessonInstructorBean liBean)
			throws UF1OperateExcetion {
		final String strSql = "insert into lesson_instructor (li_family_name,li_first_name, "
				+ "                               li_family_name_kana,li_first_name_kana,"
				+ "                               li_sex,li_birth_date,li_weight, "
				+ "                               li_profiel,li_comment,li_url,"
				+ "                               li_mail_address,li_image,pc_id) "
				+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Object[] params = { liBean.getLi_family_name(),
				liBean.getLi_first_name(), liBean.getLi_family_name_kana(),
				liBean.getLi_first_name_kana(), liBean.getLi_sex(),
				liBean.getLi_birth_date(), liBean.getLi_weight(),
				liBean.getLi_profiel(), liBean.getLi_comment(),
				liBean.getLi_url(), liBean.getLi_mail_address(),
				liBean.getLi_image(), liBean.getPc_id() };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * アップデート update
	 * 
	 * @param liBean
	 * @return Success:true, Failure:false
	 * @throws UF1OperateExcetion
	 */
	public boolean updateInstructor(LessonInstructorBean liBean)
			throws UF1OperateExcetion {
		final String strSql = "update lesson_instructor set "
				+ "    li_family_name = ?," + "    li_first_name = ?,"
				+ "    li_family_name_kana = ?,"
				+ "    li_first_name_kana = ?," + "    li_sex = ?,"
				+ "    li_birth_date = ?," + "    li_weight = ?,"
				+ "    li_profiel = ?," + "    li_comment = ?,"
				+ "    li_url = ?," + "    li_mail_address = ?,"
				+ "    li_image = ?," + "    pc_id = ?" + " where li_id = ? ";
		Object[] params = { liBean.getLi_family_name(),
				liBean.getLi_first_name(), liBean.getLi_family_name_kana(),
				liBean.getLi_first_name_kana(), liBean.getLi_sex(),
				liBean.getLi_birth_date(), liBean.getLi_weight(),
				liBean.getLi_profiel(), liBean.getLi_comment(),
				liBean.getLi_url(), liBean.getLi_mail_address(),
				liBean.getLi_image(), liBean.getPc_id(), liBean.getLi_id() };

		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * アップデート表示フラグ update active flag 表示状態変更用 to alter 'active flag'.
	 * 
	 * @param liBean
	 * @return Success:true, Failure:false
	 * @throws UF1OperateExcetion
	 */
	public boolean updateInstructorActiveFlag(Integer li_id,
			Boolean li_active_flag) throws UF1OperateExcetion {
		final String strSql = "update lesson_instructor set "
				+ "    li_active_flag = ? " + "  where li_id = ? ";
		Object[] params = { li_active_flag, li_id };

		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/*------------------------------------------------------------
	 *  会場 Place 
	 ------------------------------------------------------------*/
	/**
	 * select文
	 * 
	 * @param lpscBean
	 *            検索条件クラス。nullの場合、全検索。
	 * @return List of LessonPlaceBean
	 * @throws UF1OperateExcetion
	 */
	/*
	 * public List selectPlace(LessonPlaceSearchConditionBean lpscBean) throws
	 * UF1OperateExcetion{ //検索条件のSQLを追加 Object[] params = null; String strSql =
	 * LessonPlaceSearchConditionBean.getStrSqlSelect(); if(lpscBean != null){
	 * strSql += lpscBean.getSearchConditionSQL(); params =
	 * lpscBean.getSearchConditionObjectList(); }
	 * 
	 * try { return DBUtils.select(strSql, LessonPlaceBean.class, params,
	 * getConnection()); } catch (SQLException e) { throw new
	 * UF1OperateExcetion(e); } catch (DBException e) { throw new
	 * UF1OperateExcetion(e); } }
	 */
	/**
	 * select文
	 * 
	 * @param lp_id
	 *            検索する
	 * @return LessonPlaceBean
	 * @throws UF1OperateExcetion
	 */
	public LessonPlaceBean selectPlace(Integer lp_id) throws UF1OperateExcetion {
		LessonPlaceSearchConditionBean lpscBean = new LessonPlaceSearchConditionBean(
				lp_id);
		List lstRet = select(lpscBean);
		if (lstRet.size() == 0) {
			return null;
		} else {
			return (LessonPlaceBean) lstRet.get(0);
		}
	}

	/**
	 * select文 会場の全検索を行う
	 * 
	 * @return LessonPlaceBean
	 * @throws UF1OperateExcetion
	 */
	public List selectPlace() throws UF1OperateExcetion {
		LessonPlaceSearchConditionBean lpscBean = new LessonPlaceSearchConditionBean();
		return select(lpscBean);
	}

	/**
	 * select文 IDと名前のみ全検索を行う
	 * 
	 * @return List of LessonPlaceBean (only 'lp_id' and 'lp_name' are filled)
	 * @throws UF1OperateExcetion
	 */
	public List selectPlaceIDName() throws UF1OperateExcetion {
		String strSql = "select lp_id, lp_name from lesson_place ";

		try {
			return DBUtils.select(strSql, LessonPlaceBean.class, null,
					getConnection());
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * insert 文
	 * 
	 * @param lpBean
	 * @return Success:true, Failure:false
	 * @throws UF1OperateExcetion
	 */
	public boolean insertPlace(LessonPlaceBean lpBean)
			throws UF1OperateExcetion {
		final String strSql = "insert into lesson_place (lp_name,zip_id,lp_address,lp_phone_number1,lp_phone_number2,lp_phone_number3,lp_url,lp_image) "
				+ "values (?, ?, ?, ?, ?, ?, ?, ?)";

		Object[] params = { lpBean.getLp_name(), lpBean.getZip_id(),
				lpBean.getLp_address(), lpBean.getLp_phone_number1(),
				lpBean.getLp_phone_number2(), lpBean.getLp_phone_number3(),
				lpBean.getLp_url(), lpBean.getLp_image(), };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * update 文。 activeフラグは変更しないので注意。
	 * 
	 * @param lpBean
	 * @return Success:true, Failure:false
	 * @throws UF1OperateExcetion
	 */
	public boolean updatePlace(LessonPlaceBean lpBean)
			throws UF1OperateExcetion {
		final String strSql = "update lesson_place set " + "    lp_name = ?,"
				+ "    zip_id = ?," + "    lp_address = ?,"
				+ "    lp_phone_number1 = ?," + "    lp_phone_number2 = ?,"
				+ "    lp_phone_number3 = ?," + "    lp_url = ?,"
				+ "    lp_image = ? " + "  where lp_id = ?";
		Object[] params = { lpBean.getLp_name(), lpBean.getZip_id(),
				lpBean.getLp_address(), lpBean.getLp_phone_number1(),
				lpBean.getLp_phone_number2(), lpBean.getLp_phone_number3(),
				lpBean.getLp_url(), lpBean.getLp_image(), lpBean.getLp_id() };

		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * * 表示状態変更用 to alter 'active flag'.
	 * 
	 * @param lp_id
	 * @param lp_active_flag
	 * @return Success:true, Failure:false
	 * @throws UF1OperateExcetion
	 */
	public boolean updatePlaceActiveFlag(Integer lp_id, Boolean lp_active_flag)
			throws UF1OperateExcetion {
		final String strSql = "update lesson_place set "
				+ "    lp_active_flag = ? " + "  where lp_id = ?";
		Object[] params = { lp_active_flag, lp_id };

		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/*------------------------------------------------------------
	 *  イベント Event 
	 ------------------------------------------------------------*/

	/**
	 * select lesson_event by le_id
	 * 
	 * @param le_id
	 * @return
	 * @throws UF1OperateExcetion
	 */
	public List selectEventByLe_id(Integer le_id) throws UF1OperateExcetion {
		LessonEventSearchConditionBean leScBean = new LessonEventSearchConditionBean(
				le_id);
		return select(leScBean);
	}

	/**
	 * @param leBean
	 * @return 成功したらtrue, 挿入できなかったらfalse
	 * @throws UF1OperateExcetion
	 */
	public boolean insertEvent(LessonEventBean leBean)
			throws UF1OperateExcetion {
		final String strSql = "insert into lesson_event(li_id, lp_id, le_date, le_start_time, "
				+ "                         le_lesson_time,le_lesson_days, "
				+ "                         le_charge,le_detail,le_fixed_number "
				+ "                        ) "
				+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?)";

		Object[] params = { leBean.getLi_id(), leBean.getLp_id(),
				leBean.getLe_date(), leBean.getLe_start_time(),
				leBean.getLe_lesson_time(), leBean.getLe_lesson_days(),
				leBean.getLe_charge(), leBean.getLe_detail(),
				leBean.getLe_fixed_number(), };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	public boolean updateEvent(LessonEventBean leBean)
			throws UF1OperateExcetion {
		final String strSql = "update lesson_event set " + "    li_id = ?, "
				+ "    lp_id = ?," + "    le_date = ?,"
				+ "    le_start_time = ?," + "    le_lesson_time = ?,"
				+ "    le_lesson_days = ?," + "    le_charge = ?,"
				+ "    le_detail = ?," + "    le_fixed_number = ? "
				+ "where le_id = ?";

		Object[] params = { leBean.getLi_id(), leBean.getLp_id(),
				leBean.getLe_date(), leBean.getLe_start_time(),
				leBean.getLe_lesson_time(), leBean.getLe_lesson_days(),
				leBean.getLe_charge(), leBean.getLe_detail(),
				leBean.getLe_fixed_number(), leBean.getLe_id() };

		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * イチオシイベントの更新用
	 * 
	 * @param bRecommendFlag
	 * @param dRecommendLimitDate
	 * @param le_id
	 * @return
	 * @throws UF1OperateExcetion
	 */
	public boolean updateEventRecommend(Boolean bRecommendFlag,
			Date dRecommendLimitDate, Integer le_id) throws UF1OperateExcetion {
		final String strSql = "update lesson_event set "
				+ "    le_recommend_flag = ?," + "    le_recommend_date = ? "
				+ "where le_id = ?";

		Object[] params = { bRecommendFlag, dRecommendLimitDate, le_id };

		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * イチオシイベントの更新用
	 * 
	 * @param bRecommendFlag
	 * @param dRecommendLimitDate
	 * @param le_id
	 * @return
	 * @throws UF1OperateExcetion
	 */
	public boolean updateEventRecommend(Boolean bRecommendFlag, Integer le_id)
			throws UF1OperateExcetion {
		final String strSql = "update lesson_event set "
				+ "    le_recommend_flag = ?" + "where le_id = ?";

		Object[] params = { bRecommendFlag, le_id };

		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * le_idでレッスンイベントを削除。 ただし、予約者がいる場合は削除できない。 （外部キー参照されるため。）
	 * 
	 * @param le_id
	 * @return
	 * @throws UF1OperateExcetion
	 */
	public boolean deleteEvent(Integer le_id) throws UF1OperateExcetion {
		final String strSql = "delete from lesson_event where le_id = ?";
		Object[] params = { le_id };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet == 1;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/*------------------------------------------------------------
	 *  インストラクターと会場の関係 relation between Instructor and Place 
	 ------------------------------------------------------------*/
	/**
	 * select文
	 * 
	 * @param lpscBean
	 *            検索条件クラス。nullの場合、全検索。
	 * @return List of LessonPlaceBean
	 * @throws UF1OperateExcetion
	 */
	/*
	 * public List select(LessonInstPlaceRelationSearchConditionBean liprScBean)
	 * throws UF1OperateExcetion{ //検索条件のSQLを追加 Object[] params = null; String
	 * strSql = LessonInstPlaceRelationSearchConditionBean.getStrSqlSelect();
	 * if(liprScBean != null){ strSql += liprScBean.getSearchConditionSQL();
	 * params = liprScBean.getSearchConditionObjectList(); }
	 * 
	 * try { return DBUtils.select(strSql, LessonPlaceBean.class, params,
	 * getConnection()); } catch (SQLException e) { throw new
	 * UF1OperateExcetion(e); } catch (DBException e) { throw new
	 * UF1OperateExcetion(e); } }
	 */
	/**
	 * select by lp_id 文
	 * 
	 * @param lp_id
	 *            検索する
	 * @return LessonPlaceBean
	 * @throws UF1OperateExcetion
	 */
	public List selectInstPlaceRelationByLp_id(Integer lp_id)
			throws UF1OperateExcetion {
		LessonInstPlaceRelationSearchConditionBean liprScBean = new LessonInstPlaceRelationSearchConditionBean(
				lp_id, null);
		return select(liprScBean);
	}

	/**
	 * select by li_id 文
	 * 
	 * @param li_id
	 *            検索する
	 * @return LessonPlaceBean
	 * @throws UF1OperateExcetion
	 */
	public List selectInstPlaceRelationByLi_id(Integer li_id)
			throws UF1OperateExcetion {
		LessonInstPlaceRelationSearchConditionBean liprScBean = new LessonInstPlaceRelationSearchConditionBean(
				null, li_id);
		return select(liprScBean);
	}

	/**
	 * SELECT currval('lesson_instructor_li_id_seq')
	 * 
	 * @return 前回insertを行ったレコードのli_id
	 * @throws UF1OperateExcetion
	 */
	public Integer getCurrvalOfLi_id() throws UF1OperateExcetion {
		final String strSql = "SELECT currval('lesson_instructor_li_id_seq') as li_id";
		List lstRet = null;
		try {
			lstRet = DBUtils.select(strSql, LessonInstPlaceRelationBean.class,
					null, getConnection());
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}

		if (lstRet.size() == 0) {
			return null;
		} else {
			return ((LessonInstPlaceRelationBean) lstRet.get(0)).getLi_id();
		}
	}

	/**
	 * @param liprBean
	 * @return 成功したらtrue, 挿入できなかったらfalse
	 * @throws UF1OperateExcetion
	 */
	public boolean insertInstPlaceRelation(LessonInstPlaceRelationBean liprBean)
			throws UF1OperateExcetion {
		final String strSql = "insert into lesson_instructor_place_relation (li_id, lp_id)"
				+ "values (?, ?)";
		Object[] params = { liprBean.getLi_id(), liprBean.getLp_id() };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	public void deleteInstPlaceRelation(Integer li_id)
			throws UF1OperateExcetion {
		final String strSql = "delete from lesson_instructor_place_relation where li_id = ?";
		Object[] params = { li_id };
		try {
			DBUtils.update(getConnection(), strSql, params);
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/*------------------------------------------------------------
	 *  予約内容 reservation 
	 ------------------------------------------------------------*/
	/**
	 * 
	 * @param le_id
	 * @return List of LessonReservationBean
	 * @throws UF1OperateExcetion
	 */
	public List selectReservationByLe_id(Integer le_id)
			throws UF1OperateExcetion {
		LessonReservationSearchConditionBean lrScBean = LessonReservationSearchConditionBean
				.createLrScBeanByLe_id(le_id);
		return lrScBean.select();
	}

	public LessonReservationBean selectReservationByLr_id(Integer lr_id)
			throws UF1OperateExcetion {
		LessonReservationSearchConditionBean lrScBean = LessonReservationSearchConditionBean
				.createLrScBeanByLr_id(lr_id);
		LessonReservationBean lrBean = null;

		List lstRet = lrScBean.select();
		if (lstRet.size() != 0) {
			lrBean = (LessonReservationBean) lstRet.get(0);
		} else {
			lrBean = null;
		}
		return lrBean;
	}

	public UserMaster selectUser_info(String u_mailaddress)
			throws UF1OperateExcetion {
		final String strSql = "select u_id,u_mailaddress, u_family_name, u_first_name,"
				+ "        u_family_name_kana, u_first_name_kana"
				+ "        from user_master"
				+ "        where u_mailaddress = ?";
		Object params[] = { u_mailaddress };

		List lstRet = null;
		try {
			lstRet = DBUtils.select(strSql, UserMaster.class, params,
					getConnection());
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}

		if (lstRet.size() == 0) {
			return null;
		} else {
			return (UserMaster) lstRet.get(0);
		}

	}

	/**
	 * @param lrBean
	 * @return 成功したらtrue, 挿入できなかったらfalse
	 * @throws UF1OperateExcetion
	 */
	public boolean insertReservation(LessonReservationBean lrBean)
			throws UF1OperateExcetion {
		final String strSql = "insert into lesson_reservation ("
				+ "         le_id, u_id, "
				+ "         lr_u_mailaddress, lr_family_name, lr_first_name, lr_family_name_kana, lr_first_name_kana,"
				+ "         lr_kikiude, lr_handicap, lr_average_score, "
				+ "         lr_phone_number1, lr_phone_number2, lr_phone_number3, "
				+ "         lr_credit_card_number, lr_credit_card_limit, lr_remark)"
				+ "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		Object[] params = { lrBean.getLe_id(), lrBean.getU_id(),
				lrBean.getLr_u_mailaddress(), lrBean.getLr_family_name(),
				lrBean.getLr_first_name(), lrBean.getLr_family_name_kana(),
				lrBean.getLr_first_name_kana(), lrBean.getLr_kikiude(),
				lrBean.getLr_handicap(), lrBean.getLr_average_score(),
				lrBean.getLr_phone_number1(), lrBean.getLr_phone_number2(),
				lrBean.getLr_phone_number3(),
				lrBean.getLr_credit_card_number(),
				lrBean.getLr_credit_card_limit(), lrBean.getLr_remark() };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * @param lrBean
	 * @return 成功したらtrue, 挿入できなかったらfalse
	 * @throws UF1OperateExcetion
	 */
	public boolean updateReservation(LessonReservationBean lrBean)
			throws UF1OperateExcetion {
		final String strSql = "update lesson_reservation set"
				+ "               le_id = ?," + "        	    u_id = ?,"
				+ "       	    lr_u_mailaddress = ?,"
				+ "        	    lr_family_name = ?,"
				+ "        	    lr_first_name = ?,"
				+ "        	    lr_family_name_kana = ?,"
				+ "        	    lr_first_name_kana = ?,"
				+ "        	    lr_kikiude = ?,"
				+ "        	    lr_handicap = ?,"
				+ "        	    lr_average_score = ?,"
				+ "        	    lr_phone_number1 = ?,"
				+ "        	    lr_phone_number2 = ?,"
				+ "        	    lr_phone_number3 = ?,"
				+ "        	    lr_credit_card_number = ?,"
				+ "        	    lr_credit_card_limit = ?,"
				+ "        	    lr_remark = ?" + "        	where lr_id = ?";

		Object[] params = { lrBean.getLe_id(), lrBean.getU_id(),
				lrBean.getLr_u_mailaddress(), lrBean.getLr_family_name(),
				lrBean.getLr_first_name(), lrBean.getLr_family_name_kana(),
				lrBean.getLr_first_name_kana(), lrBean.getLr_kikiude(),
				lrBean.getLr_handicap(), lrBean.getLr_average_score(),
				lrBean.getLr_phone_number1(), lrBean.getLr_phone_number2(),
				lrBean.getLr_phone_number3(),
				lrBean.getLr_credit_card_number(),
				lrBean.getLr_credit_card_limit(), lrBean.getLr_remark(),
				lrBean.getLr_id() };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet != 0;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * lr_idでレッスンイベント予約者を削除。
	 * 
	 * @param le_id
	 * @return
	 * @throws UF1OperateExcetion
	 */
	public boolean deleteReservation(Integer lr_id) throws UF1OperateExcetion {
		final String strSql = "delete from lesson_reservation where lr_id = ?";
		Object[] params = { lr_id };
		try {
			int iRet = DBUtils.update(getConnection(), strSql, params);
			return iRet == 1;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/*
	 * その他
	 */
	/**
	 * 県名・IDの一覧を取得
	 */
	public List selectPrefecture() throws UF1OperateExcetion {
		final String strSql = "select pc_id, pc_name from prefecture";

		try {
			return DBUtils.select(strSql, Prefecture.class, null,
					getConnection());
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}

	}

	/**
	 * 引数で指定したzip_idが有効かどうかを判定。 有効なら true, 無効ならfalseをリターン。
	 * 
	 * @param zip_id
	 * @return
	 * @throws UF1OperateExcetion
	 */
	public boolean existZip_id(String zip_id) throws UF1OperateExcetion {
		final String strSql = "select zip_id from ziptable where zip_id = ?";
		Object[] params = { zip_id };
		try {
			List lstRet = DBUtils.select(strSql, Ziptable.class, params,
					getConnection());
			return lstRet.size() >= 1;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}

	/**
	 * メールアドレスに対応する会員が存在するかどうかチェック
	 * 
	 * @param u_mailaddress
	 * @return
	 * @throws UF1OperateExcetion
	 */
	public boolean existUser(String u_mailaddress) throws UF1OperateExcetion {
		final String strSql = "select u_id from user_master where u_mailaddress = ? and u_active = true";
		Object[] params = { u_mailaddress };
		try {
			List lstRet = DBUtils.select(strSql, UserMaster.class, params,
					getConnection());
			return lstRet.size() >= 1;
		} catch (SQLException e) {
			throw new UF1OperateExcetion(e);
		} catch (DBException e) {
			throw new UF1OperateExcetion(e);
		}
	}
}
